﻿using BCCommon;
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BCData
{
    /// <summary>
    /// 生成SQL语句
    /// </summary>
    public static class SqlCommands
    {
        public static async Task<(IEnumerable<T1>, T2)> QueryMultipleAsync<T1, T2>(this IDatabaseContext context, string sqlTextOrScriptName, DynamicParameters parameters = null, CommandType commandType= CommandType.Text)
        {
            using (var multi = await context.Master.QueryMultipleAsync(sqlTextOrScriptName, parameters, commandType: commandType))
            {
                return (multi.Read<T1>(), multi.Read<T2>().FirstOrDefault());
            }
        }

        public static Tuple<IEnumerable<T1>, T2> QueryMultiple<T1, T2>(this IDatabaseContext context, string sqlTextOrScriptName, DynamicParameters parameters = null, CommandType commandType = CommandType.Text)
        {
            using (var results = context.Master.QueryMultiple(sqlTextOrScriptName, parameters, commandType: commandType))
            {
                if (results == null)
                {
                    return null;
                }

                return Tuple.Create(results.Read<T1>(), results.ReadFirstOrDefault<T2>());
            }
        }

        public static string GetInsertSql(string tableName, string[] fieldNames)
        {
            return string.Format("insert into {0} ({1}) Values (?{2});", tableName, string.Join(",", fieldNames), string.Join(",?", fieldNames).Replace("`", ""));
        }

        public static string GetUpdateSql(string tableName, string[] modifyFields, string[] conditionFields)
        {
            var modifyFieldStr = string.Join(",", modifyFields.Select(v => string.Format(" {0}=?{0} ", v)).ToArray());
            var conditionFieldStr = string.Join("and", conditionFields.Select(v => string.Format(" {0}=?{0} ", v)).ToArray());
            return string.Format("Update {0} Set {1} Where {2};", tableName, modifyFieldStr, conditionFieldStr);
        }

        public static string GetQuerySql(string tableName, string[] fieldNames, string[] conditionFields, string orderByfieldName, bool? ascending = null)
        {
            string sql = string.Format("select {0} from {1} ", string.Join(",", fieldNames), tableName);

            if (conditionFields != null && conditionFields.Any())
            {
                List<string> wheres = new List<string>();
                for (int i = 0; i < conditionFields.Length; i++)
                {
                    wheres.Add(string.Format("{0}=?{0}", conditionFields[i]));
                }
                sql += conditionFields.Length > 0 ? " WHERE " + string.Join(" AND ", wheres) : "";
            }
            if (!string.IsNullOrWhiteSpace(orderByfieldName))
            {
                sql += "Order By " + orderByfieldName;
            }
            if (ascending.HasValue)
            {
                sql += ascending.Value ? " ASC" : " DESC";
            }
            return sql;
        }
    }
}
